using System;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using System.Data;
using Newtonsoft.Json;
using T.FAS.Runtime.Base.Entity;
using T.FAS.Runtime.Expression.Entity;
using T.FAS.Runtime.Expression.Service;
using T.FAS.Runtime.Metadata.Entity.DataModel;
//using T.FAS.Authority.Metadata.Entity;
//using T.FAS.Authority.MetaData.Dev.Plugin;
using T.FAS.Runtime.DataAccess.SPI;
using T.FAS.Runtime.DataAccess.SQLDom;
using T.FAS.Runtime.DataAccess.Kernel;
using T.FAS.Runtime.DataAccess.Common;
using RelationalOperator = T.FAS.Runtime.DataAccess.SQLDom.RelationalOperator;
using T.FAS.Runtime.DataAccess.Engine.ValidateChecker.Entity;

namespace T.FAS.Runtime.DataAccess.Engine
{
    class DQQueryService : IModelQueryService<DataQuery>
    {
        public readonly static string AdvancedFilterString = DACConst.DQ.AdvancedFilterString;
        public readonly static string AdvancedSortString = DACConst.DQ.AdvancedSortString;
        public readonly static string DefaultInputString = DACConst.DQ.DefaultInputString;
        public readonly static string ExtSettingString = DACConst.ExtSetting;

        public event ExecuteDataAccessAction Execute;
        public event BuildDataAccessActionContext BuildContext;

        //private readonly ActionPermissionDefinitionManager _permissionManager = new ActionPermissionDefinitionManager();
        //private readonly IDataPermissionsService _permissionsService = DataPermissionsService.GetInstance();
        private readonly IDACLogService _localLogService = DACLogFactory.GetDACLogService();

        /// <summary>
        /// DQ SQL查看
        /// </summary>
        /// <param name="metadata"></param>
        /// <param name="extParam"></param>
        /// <returns></returns>
        public string GetQueryString(DataQuery metadata, Dictionary<string, object> extParam = null)
        {
            DataQueryControlContext controlContext = InitDataQueryControlContext(extParam, metadata);
            //step 3 处理当前DQ所有条件&高级查询条件
            var sqlstatement = QuerySQLBuildStrategy.BuildTableSqlSchema(
                                                            metadata,
                                                            null,
                                                            controlContext,
                                                            out bool isCanPage,
                                                            out bool isHaveFilter,
                                                            out DataQuery virDq,
                                                            0,
                                                            0,
                                                            null,
                                                            null,
                                                            null,
                                                            null
                                                            ) as SelectSqlStatement;

            return sqlstatement.ToSQL();
        }

        /// <summary>
        /// DQ查询入口
        /// </summary>
        /// <param name="metadata"></param>
        /// <param name="hierarchyParams"></param>
        /// <param name="extPara"></param>
        /// <returns></returns>
        public TResult Query(DataQuery metadata, Dictionary<string, object> hierarchyParams, Dictionary<string, object> extPara = null)
        {
            DateTime start = DateTime.Now;
            Exception ex = null;

            try
            {
                metadata = metadata.Clone() as DataQuery;
                //重新构建DQ引用关系
                metadata.RebuildRelationForEngine();
                //
                PreprecoessDQ(metadata, hierarchyParams);
                DataQueryControlContext controlContext = InitDataQueryControlContext(extPara, metadata);
                var result = Query(metadata, null, null, out SubQueryConditionStatement sub, out DataQuery virDq, hierarchyParams, controlContext);

                var mainQueryResult = result.LastOrDefault();
                var allTables = result.Select(ds => ds?.Tables[0]).Where(t => t != null).ToList();

                int sum = 0;
                if (mainQueryResult.Tables.Count > 1)
                    sum = Convert.ToInt32(mainQueryResult.Tables[1].Rows[0][0]);
                else
                    sum = mainQueryResult.Tables[0].Rows.Count;
                TResult data;
                if (virDq == null)
                    data = DataQueryHelper.BuildTResult(metadata, allTables, sum);
                else
                    data = DataQueryHelper.BuildTResult(virDq, allTables, sum);

                return data;

            }
            catch (Exception e)
            {
                ex = new Exception($"DQ:{metadata?.Code}查询失败", e);
                DQMonitor.RootCaseReport(metadata, start, ex);
                throw ex;
            }
            finally
            {
                DQMonitor.MonitorTPS(metadata, ex);
                DQMonitor.MonitorTrace(metadata, start, ex);
                DQMonitor.MonitorDuration(metadata, (DateTime.Now - start).TotalMilliseconds, ex);
            }
        }

        /// <summary>
        /// 递归DQ执行返回DataSet，暂不需要，目前只需要两层
        /// </summary>
        /// <param name="currentQuery"></param>
        /// <param name="parentQuery"></param>
        /// <param name="subQuery"></param>
        /// <param name="outCondition"></param>
        /// <param name="virDq"></param>
        /// <param name="hierarchyParam"></param>
        /// <param name="controlContext"></param>
        /// <param name="listParam"></param>
        /// <param name="inCondition"></param>
        /// <param name="depth"></param>
        /// <param name="rootQueryRef">根查询引用，友好性报错提示用</param>
        /// <returns></returns>
        /// <exception cref="FasException"></exception>
        private List<DataSet> Query(
            DataQuery currentQuery,
            DataQuery parentQuery,
            DataQuerySubQuery subQuery,
            out SubQueryConditionStatement outCondition,
            out DataQuery virDq,
            Dictionary<string, object> hierarchyParam,   // 保留层级结构的参数
            DataQueryControlContext controlContext,      // 控制参数
            Dictionary<string, FilterParam> listParam = null, // 拉平的参数，为了拿到父子DQ中高级查询动态生成的参数
            SubQueryConditionStatement inCondition = null,
            int depth = 0,
            DataQuery rootQueryRef = null)
        {
            /*
             * 处理DQ步骤：
             * 1、处理入参
             * 2、处理权限SQL
             * 3、构造SqlStatement，考虑如何实现In Exist
             * 3、根据depth深度判断生成Exist还是In子句，（子查询条件如何通过递归向上传递，多层会递归嵌套）
             * 4、构造DB
             * 5、执行SQL查询处理结果
             * 6、递归子查询
             */
            //初始化
            List<DataSet> datas = new List<DataSet>();
            if (listParam == null)
                listParam = new Dictionary<string, FilterParam>();
            if (depth == 0 && rootQueryRef == null)
                rootQueryRef = currentQuery;
            //step 1
            DQParamResult param = ProcessParameter(currentQuery, hierarchyParam);

            //初始化掩码控制上下文
            if (param.QueryExtSetting != null)
            {
                controlContext.InitMaskControl(true, param.QueryExtSetting.IsAll, param.QueryExtSetting.Columns);
            }
            //初始高级查询filter上下文
            if (param.AdvanceQuerySQL?.AdvancedFilterExtSetting != null)
            {
                controlContext.InitAdvancedFilterExtSetting(param.AdvanceQuerySQL.AdvancedFilterExtSetting);
            }

            if (param.PrecessedParamList != null)
            {
                foreach (var key in param.PrecessedParamList.Keys)
                {
                    //除主查询外的特殊参数不处理
                    if (depth > 0)
                    {
                        if (key == AdvancedFilterString) continue;
                        if (key == AdvancedSortString) continue;
                        if (key == DefaultInputString) continue;
                        if (key == ExtSettingString) continue;
                    }
                    //if (param.PrecessedParamList[key] is Dictionary<string, object>) continue;

                    if (listParam.ContainsKey(key))
                    {
                        throw new FasException(DataAccessErrorCode.DataQuery.Error, string.Format(CommonStringClassEngine.DQQueryService_Query, key, listParam[key], param.PrecessedParamList[key]));
                    }

                    listParam[key] = param.PrecessedParamList[key];
                }
            }

            //step 2
            var authSQL = GetAuthSQLs(currentQuery);

            //step 3 处理当前DQ所有条件&高级查询条件
            var sqlstatement = QuerySQLBuildStrategy.BuildTableSqlSchema(
                                                            currentQuery,
                                                            param.VariableList,
                                                            controlContext,
                                                            out bool isCanPage,
                                                            out bool isHaveFilter,
                                                            out virDq,
                                                            param.PageSize,
                                                            param.PageIndex,
                                                            param.AdvanceQuerySQL,
                                                            param.AdvancedSorts,
                                                            authSQL,
                                                            depth == 0 ? null : subQuery.SubQueryAlias
                                                            ) as SelectSqlStatement;

            //step 4
            var db = DatabaseFactory.CreateDatabase(DBTypeConverter.Convert(currentQuery.DataQuerySources.FirstOrDefault().DataObject.Database.DbType));

            List<SubQueryConditionStatement> SubQueryOutConditions = new List<SubQueryConditionStatement>();

            #region 递归子查询，控制depth递归深度
            if (currentQuery.SubQuerys != null && currentQuery.SubQuerys.Count > 0 && depth < 1)
            {

                foreach (var childQuery in currentQuery.SubQuerys)
                {
                    if (childQuery.SubQuery == null)
                    {
                        throw new FasException(DataAccessErrorCode.DataQuery.Error, string.Format(CommonStringClassEngine.DQQueryService_Query1, currentQuery.Code, childQuery.SubQueryID));
                    }

                    var innerQuerySQLStatement = GetInSqlStatement(currentQuery, childQuery, sqlstatement);
                    if (inCondition != null) //向下传递IN条件
                        innerQuerySQLStatement.FilterCondition.ChildCollection.Add(inCondition);
                    SubQueryConditionStatement inSubQueryCondition = new SubQueryConditionStatement
                    {
                        RelationalOperator = RelationalOperator.IN,
                    };
                    inSubQueryCondition.FieldNameWraps = innerQuerySQLStatement.SqlBuildingInfo.WrapByCurrentDBEscapeCharacter;
                    var childQuerySource = childQuery.SubQuery.GetSubQuerySource(childQuery);
                    var childQueryColumn = childQuery.SubQuery.GetSubQueryColumn(childQuery);

                    inSubQueryCondition.INClauseConditionField = new ConditionField
                    {
                        Table = new SqlTable(childQuerySource.DataObject.GetTableName(), childQuerySource.Alias),
                        FieldName = childQueryColumn.DataColumn.ColumnName,
                        IsUseFieldPrefix = true,
                    };
                    inSubQueryCondition.SubQuery = innerQuerySQLStatement;

                    //从主查询中将子查询参数Param抽出来
                    Dictionary<string, object> subParamList = new Dictionary<string, object>();
                    if (hierarchyParam.ContainsKey(childQuery.SubQueryAlias))
                        subParamList = hierarchyParam[childQuery.SubQueryAlias] as Dictionary<string, object>;

                    //Recursive Point
                    var childData = Query(
                            childQuery.SubQuery,
                            currentQuery,
                            childQuery,
                            out SubQueryConditionStatement outSubQueryCondition,
                            out virDq,
                            subParamList,
                            controlContext,
                            listParam,
                            inSubQueryCondition,
                            depth + 1,
                            rootQueryRef);

                    SubQueryOutConditions.Add(outSubQueryCondition);   //拿到子查询向上传递的条件
                    datas.AddRange(childData);
                }
            }
            #endregion

            #region 向上传递Exists语句条件
            if (parentQuery != null && parentQuery.TreeListType == TreeListType.OnlyExistChilds)
            {
                var outSelectSQL = GetOutSqlStatement(currentQuery, parentQuery, subQuery, sqlstatement);
                //向上传递Exists条件
                if (SubQueryOutConditions.Count > 0)
                {
                    foreach (var subOutCondi in SubQueryOutConditions)
                    {
                        outSelectSQL.FilterCondition.ChildCollection.Add(subOutCondi);
                    }
                }
                outCondition = new SubQueryConditionStatement
                {
                    RelationalOperator = RelationalOperator.Exists
                };
                outCondition.FieldNameWraps = outSelectSQL.SqlBuildingInfo.WrapByCurrentDBEscapeCharacter;
                outCondition.SubQuery = outSelectSQL;
                outCondition.SubQueryAlias = subQuery.SubQueryAlias;

                var parentQuerySource = parentQuery.GetMainQuerySource(subQuery);
                var childQuerySource = currentQuery.GetSubQuerySource(subQuery);
                var parentQueryColumn = parentQuery.GetMainQueryColumn(subQuery);
                var childQueryColumn = currentQuery.GetSubQueryColumn(subQuery);
                outCondition.SubQueryConditionItems = new List<SubQueryConditionItem>
                {
                    new SubQueryConditionItem
                    {
                         OuterQueryField = new ConditionField
                         {
                             Table = new SqlTable(parentQuerySource.DataObject.GetTableName() , parentQuerySource.Alias),
                             FieldName = parentQueryColumn.DataColumn.ColumnName,
                             IsUseFieldPrefix = true,
                         },
                         SubQueryField = new ConditionField
                         {
                             Table = new SqlTable(childQuerySource.DataObject.GetTableName() , subQuery.SubQueryAlias),
                             FieldName = childQueryColumn.Alias,
                             IsUseFieldPrefix = true,
                         },
                    }
                };
            }
            else
            {
                outCondition = null;
            }

            #endregion

            #region 处理当前查询所有的Exists In条件
            if (SubQueryOutConditions.Count > 0)
            {
                foreach (var subOutCondi in SubQueryOutConditions)
                {
                    sqlstatement.FilterCondition.ChildCollection.Add(subOutCondi);
                }
            }

            if (inCondition != null)    //父级传入的子查询条件不为空时，拼接到SQL条件中
                sqlstatement.FilterCondition.ChildCollection.Add(inCondition);
            #endregion

            //将ListParam拉平的参数列表生成DbParameter。会包含所有主子查询In Exists子句中的条件变量
            var parameters = GetDbDataParameters(listParam, param.PrecessedParamTypeList, currentQuery.GetDQDatabase()).ToList();
            //取数
            var sql = sqlstatement.ToSQL();
            try
            {
                Dictionary<string, object> controlContextDict = BuildControlContext(currentQuery);
                if (!isHaveFilter)
                {
                    DQMonitor.MonitorNoFilter(currentQuery);
                }
                //增加额外的元数据信息用于Debug
                controlContextDict["MetaDataCode"] = currentQuery.Code ?? currentQuery.MetaDataCode;
                var data = db.GetData(sqlstatement.DatabaseConfigKey, sql, sqlstatement.TableName, parameters.ToArray(), controlContextDict);
                datas.Add(data);
                return datas;
            }
            catch (Exception e)
            {
                throw new FasException(DataAccessErrorCode.DataQuery.Error, string.Format(CommonStringClassEngine.DQQueryService_Query2, rootQueryRef?.Code, depth, currentQuery?.Code), e);
            }
        }

        #region Private Funcs

        /// <summary>
        /// 预处理DQ，如果有子查询，确保全局的别名不重复，广度优先搜索，忽略已经搜索过的DQ。
        /// </summary>
        /// <param name="dataQuery"></param>
        /// <param name="paramList"></param>
        /// <param name="record"></param>
        /// <param name="depth"></param>
        /// <exception cref="FasException"></exception>
        private void PreprecoessDQ(DataQuery dataQuery, Dictionary<string, object> paramList, HashSet<string> record = null, int depth = 0)
        {
            if (dataQuery == null)
                return;
            if (record == null)
                record = new HashSet<string>();
            record.Add(dataQuery.ID);
            if (dataQuery.SubQuerys != null && dataQuery.SubQuerys.Count > 0)
            {
                int i = 0;
                foreach (var subQuery in dataQuery.SubQuerys)
                {
                    if (!record.Contains(subQuery.SubQueryID))
                    {
                        if (EnvironmentSetting.RunningMode == RunningMode.LocalDebug)
                        {

                        }
                        else
                        {
                            subQuery.SubQuery = RTPMetadataHelper.GetRTPMetadata(subQuery.SubQueryID) as DataQuery;
                        }
                        if (subQuery.SubQuery == null)
                        {
                            throw new FasException(DataAccessErrorCode.DataQuery.Error, string.Format(CommonStringClassEngine.DQQueryService_PreprecoessDQ, dataQuery.Code, subQuery.SubQueryID));
                        }

                        //给子查询加入前缀
                        var prefix = $"SubQuery_{depth + 1}_{i++}_";
                        var subQueryAlias = prefix + subQuery.SubQueryAlias;

                        //处理参数
                        if (paramList.ContainsKey(subQuery.SubQueryAlias))
                        {
                            Dictionary<string, object> oldSubQueryParam = paramList[subQuery.SubQueryAlias] as Dictionary<string, object>;
                            //移除原有的子查询参数key
                            paramList.Remove(subQuery.SubQueryAlias);
                            if (oldSubQueryParam != null)
                            {
                                //处理DQ所有用到Param参数的地方，加入DQ子查询Alias前缀
                                subQuery.SubQuery.ProcessParamDictListWithParamPrefix(ref oldSubQueryParam, subQueryAlias);
                                subQuery.SubQuery.ProcessDataQueryWithParamPrefix(subQueryAlias);
                                //添加带有前缀的子查询参数key
                                paramList[subQueryAlias] = oldSubQueryParam;
                            }
                        }
                        //四段式确保SubQuery别名在树中唯一
                        subQuery.SubQueryAlias = subQueryAlias;
                        PreprecoessDQ(subQuery.SubQuery, paramList, record, depth + 1);
                    }
                }
            }
        }

        /// <summary>
        /// 构造控制上下文
        /// </summary>
        /// <param name="dq"></param>
        /// <returns></returns>
        private Dictionary<string, object> BuildControlContext(DataQuery dq)
        {
            Dictionary<string, object> controlInfo = new Dictionary<string, object>
            {
                {DACConst.DACControlContextConst.Timeout, dq.Timeout }
            };
            return controlInfo;
        }

        /// <summary>
        /// 初始化上下文
        /// </summary>
        /// <param name="controlContextDict"></param>
        /// <param name="metadata"></param>
        /// <returns></returns>
        private DataQueryControlContext InitDataQueryControlContext(Dictionary<string, object> controlContextDict, DataQuery metadata)
        {
            DataQueryControlContext controlContext = new DataQueryControlContext();
            if (controlContextDict != null)
            {
                if (controlContextDict.ContainsKey(DACConst.ExtParamSwitch.InternationalMode))
                {
                    controlContext.InternationalMode = (InternationalMode)controlContextDict[DACConst.ExtParamSwitch.InternationalMode];
                }
                if (controlContextDict.ContainsKey(DACConst.ExtParamSwitch.TargetLanguageList))
                {
                    controlContext.SpecifiedLanguages = controlContextDict[DACConst.ExtParamSwitch.TargetLanguageList] as List<string>;
                }
            }
            //构造鉴权上下文
            if (DACControlContextHelper.IsNeedAuthAndTryGetAuthResult(metadata, out List<ColumnPermissionView> result))
            {
                controlContext.InitAuthControl(true, result);
            }
            return controlContext;
        }

        #region 权限相关

        /// <summary>
        /// 运行时获取权限信息
        /// </summary>
        /// <param name="dqId"></param>
        /// <returns></returns>
        //private List<AuthRuntimeParam> GetDQAuthRuntimeParams(string dqId)
        //{
        //    try
        //    {
        //        return _permissionManager.GetRuntimeParam(dqId);
        //    }
        //    catch (Exception e)
        //    {

        //        throw new FasException(DataAccessErrorCode.DataQuery.Error, string.Format(CommonStringClassEngine.DQQueryService_GetDQAuthRuntimeParams, dqId), e);
        //    }
        //}

        /// <summary>
        /// 运行时对接权限，获取未处理的权限sql集合
        /// </summary>
        /// <param name="dqId"></param>
        /// <returns></returns>
        private List<string> GetUnprocessedDQAuthSQL(string dqId)
        {
            //List<AuthRuntimeParam> permissionData = GetDQAuthRuntimeParams(dqId);
            //if (permissionData == null || permissionData.Count == 0) return null;

            List<string> authSqls = new List<string>();
            /*
             * 低代码DQ授权有两种复用模式，DPO选择【复用权限的BPO】后
             * 1、如果不添加DPD，则只传入functionid
             * 2、DQ根据绑定的数据权限的【是否启用明细权限控制】启用情况判断：
             *      2.1 如果全部未启用或全部启用，则认为走老逻辑，只传functionid
             *      2.2 如果部分启用
             */
            //var funcGroups = permissionData.GroupBy(p => p.FunctionId);
            //foreach (var group in funcGroups)
            //{
            //    string funcId = group.Key;
            //    string permId = string.Empty;
            //    string sql;
            //    try
            //    {
            //        var authParams = group.Where(auth => !string.IsNullOrEmpty(auth.DpdId) && auth.IsEnabled).ToList();

            //        if (authParams.Count == 0 || authParams.Count == group.Count())
            //        {
            //            sql = _permissionsService.GetFilterSqlByUserId(UserUtil.GetCurrentUserID(), funcId);
            //            authSqls.Add(sql);
            //        }
            //        else
            //        {
            //            foreach (var param in authParams)
            //            {
            //                permId = param.DpdId;
            //                sql = _permissionsService.GetFilterSqlByUserId(UserUtil.GetCurrentUserID(), funcId, permId);
            //                authSqls.Add(sql);
            //            }
            //        }
            //    }
            //    catch (Exception e)
            //    {
            //        throw new FasException(DataAccessErrorCode.DataQuery.Error,
            //                string.Format(CommonStringClassEngine.DQQueryService_GetUnprocessedDQAuthSQL,
            //                    dqId, funcId + (string.IsNullOrEmpty(permId) ? "" : $":{permId}"), UserUtil.GetCurrentUserID(), e.Message), e);
            //    }
            //}

            return authSqls;
        }

        /// <summary>
        /// 获取DQ权限sql
        /// </summary>
        /// <param name="metadata"></param>
        /// <returns></returns>
        private string GetAuthSQLs(DataQuery metadata)
        {
            //没有启用权限过滤时直接返回
            if (!metadata.IsPermissionControl) return string.Empty;
            List<string> authResult = GetUnprocessedDQAuthSQL(metadata.ID);
            if (authResult != null && authResult.Count > 0)
            {
                _localLogService.Write($"DQ:{metadata.Code}开启权限管理，获取的权限SQL内容为:\r\n{string.Join("\r\n", authResult)}\r\n");
                //因为权限系统获取sql都带着with(nolock)，需要根据DQ的数据库类型进行替换
                authResult = ProcessAuthSqls(metadata, authResult);
                _localLogService.Write($"DQ:{metadata.Code}开启权限管理，替换后的权限SQL内容为:\r\n{string.Join("\r\n", authResult)}\r\n");
            }
            StringBuilder sb = new StringBuilder();
            if (authResult != null && authResult.Count > 0)
            {
                for (int i = 0; i < authResult.Count; i++)
                {
                    sb.Append("(");
                    sb.Append(authResult[i]);
                    sb.Append(")");
                    if (i < authResult.Count - 1)
                        sb.Append(" and ");
                }
            }
            return sb.ToString();
        }

        /// <summary>
        /// 处理权限SQL
        /// </summary>
        /// <param name="dq"></param>
        /// <param name="authSQLs"></param>
        /// <returns></returns>
        private List<string> ProcessAuthSqls(DataQuery dq, List<string> authSQLs)
        {
            if (authSQLs == null || authSQLs.Count == 0) return authSQLs;
            if (dq == null) return authSQLs;
            string dbType = dq.DataQuerySources.FirstOrDefault(s => s.IsMainSource)?.DataObject?.Database?.DbType;
            DatabaseType databaseType;
            if (string.IsNullOrEmpty(dbType))
                databaseType = DatabaseType.SQLServer;
            else
                databaseType = (DatabaseType)Enum.Parse(typeof(DatabaseType), dbType);

            List<string> result = new List<string>();
            foreach (var sql in authSQLs)
            {
                if (databaseType == DatabaseType.MySQL || databaseType == DatabaseType.PostgreSQL)
                {
                    //sys权限sql返回带着 with(nolock) ，需要替换掉
                    result.Add(sql.Replace("with(nolock)", ""));
                }
                else
                {
                    result.Add(sql);
                }
            }
            return result;
        }
        #endregion

        #region 参数处理相关

        /// <summary>
        /// DQ参数进行参数变量替换
        /// </summary>
        /// <param name="paramList"></param>
        /// <param name="types">DQ元数据Param参数列表的参数&类型，不包含高级查询参数</param>
        /// <param name="databaseType"></param>
        /// <returns></returns>
        private IDbDataParameter[] GetDbDataParameters(Dictionary<string, FilterParam> paramList, Dictionary<string, string> types, DatabaseType databaseType)
        {
            var paramColle = new List<IDbDataParameter>();
            foreach (var item in paramList)
            {
                if (item.Key == AdvancedFilterString) continue;//不需要处理AdvancedFilter
                //if (paramValue is Dictionary<string, object>) continue; //子查询参数不需要处理

                DataType dataType = DataType.String;
                if (types.TryGetValue(item.Key, out string dataTypeStr))
                {
                    if (Enum.TryParse(dataTypeStr, out DataType dType))
                    {
                        dataType = dType;
                    }
                }

                IDbDataParameter par = SampleDBFactory.GetParameter(databaseType, item.Value.DatabaseColumnType);
                //IDbDataParameter par = SampleDBFactory.GetParameter(databaseType, dataType);
                par.ParameterName = "@" + item.Key;
                var paramValue = item.Value.ParamValue;

                //warn:目前暂时忘了为什么value都要改为string类型
                //DQ本身的参数列表处理
                if (types.ContainsKey(item.Key))
                {
                    switch (types[item.Key].ToLower())
                    {
                        case "string":
                            par.Value = Convert.ToString(paramValue);
                            break;
                        case "boolean":
                            par.Value = paramValue == null ? null : Convert.ToBoolean(paramValue) == true ? "1" : "0";
                            break;
                        case "int":
                        case "long":
                        case "decimal":
                            par.Value = GetNumericParameterValue(databaseType, dataType, paramValue);
                            break;
                        case "date":
                            par.Value = GetDateTimeTypeDefaultValue(paramValue, databaseType, "yyyy-MM-dd");
                            break;
                        case "time":
                            par.Value = GetDateTimeTypeDefaultValue(paramValue, databaseType, "HH:mm:ss.fff");
                            break;
                        case "datetime":
                            par.Value = GetDateTimeTypeDefaultValue(paramValue, databaseType, "yyyy-MM-dd HH:mm:ss.fff");
                            break;
                        case "timestamp":
                            par.Value = GetDateTimeTypeDefaultValue(paramValue, databaseType, "yyyy-MM-dd HH:mm:ss.fffff");
                            break;
                        case "binary":
                            par.Value = Convert.ToString(paramValue);
                            break;
                        default:
                            par.Value = paramValue;
                            break;
                    }
                }
                //高级查询参数
                else
                {
                    if (paramValue == null)
                        par.Value = "";
                    else
                    {
                        if (DataTypeToolFactory.GetSupportedCSharpTypes().Contains(paramValue.GetType()))
                            par.Value = paramValue;
                        else
                            par.Value = paramValue.ToString();
                    }
                }
                paramColle.Add(par);
            }
            return paramColle.ToArray();
        }

        /// <summary>
        /// 获取DateTime类型的默认值
        /// </summary>
        /// <param name="value"></param>
        /// <param name="databaseType"></param>
        /// <param name="format"></param>
        /// <returns></returns>
        private object GetDateTimeTypeDefaultValue(object value, DatabaseType databaseType, string format)
        {
            if (value != null)
            {
                if (databaseType == DatabaseType.PostgreSQL)
                    return Convert.ToDateTime(value);
                else
                    return Convert.ToDateTime(value).ToString(format);
            }
            string dateTimeDefaultValue = null;
            switch (databaseType)
            {
                case DatabaseType.SQLServer:
                    dateTimeDefaultValue = "1753-01-01 00:00:00.000";
                    break;
                case DatabaseType.PostgreSQL:
                    dateTimeDefaultValue = "1900-01-01 00:00:00.000";
                    break;
                case DatabaseType.MySQL:
                case DatabaseType.Oracle:
                case DatabaseType.ES:
                case DatabaseType.HBase:
                case DatabaseType.TiDB:
                default:
                    break;
            }

            if (databaseType == DatabaseType.PostgreSQL)
                return Convert.ToDateTime(dateTimeDefaultValue);
            else
                return Convert.ToDateTime(dateTimeDefaultValue).ToString(format);
        }

        /// <summary>
        /// 处理DQ查询参数，返回参数结果
        /// </summary>
        /// <param name="metadata"></param>
        /// <param name="paramList"></param>
        /// <returns></returns>
        private DQParamResult ProcessParameter(DataQuery metadata, Dictionary<string, object> paramList)
        {
            var errorCode = $"DQ:{metadata?.Code}";
            var result = new DQParamResult();
            AdvanceQuerySQL advanceQuerySQL = default;
            List<AdvancedSort> advancedSorts = null;
            bool isPaging = false;
            int pageSize = 0;
            int pageIndex = 0;

            #region 处理分页参数，同时根据是否存在分组列 判断是否执行分页查询
            if (paramList != null && paramList.ContainsKey("PageSize"))
            {
                try
                {
                    pageSize = Convert.ToInt32(paramList["PageSize"]);
                    isPaging = pageSize > 0 ? true : false;
                    pageIndex = paramList.ContainsKey("PageIndex") ? Convert.ToInt32(paramList["PageIndex"]) : 0;
                    //对系统默认参数 页码 页大小做默认值赋值，防止前端未传入PageIndex&PageSize时，构造dbparm的value为null，导致ado.net转换参数类型失败的bug
                    paramList["PageSize"] = pageSize;
                    paramList["PageIndex"] = pageIndex;
                }
                catch (Exception e)
                {
                    throw new Exception($"fail to process paging param, please check PageIndex|PageSize paramters, errmsg:{e.Message}", e);
                }
            }
            var groupCols = metadata.DataQueryColumns.Where(x => x.ColumnType == ColumnType.Group).ToList();
            if (groupCols != null && groupCols.Count > 0)
            {
                //分组group可以支持分页
                //result.IsPaging = false;
                //result.PageSize = 0;
                result.IsPaging = isPaging;
                result.PageSize = pageSize;
                result.PageIndex = pageIndex;
            }
            else
            {
                result.IsPaging = isPaging;
                result.PageSize = pageSize;
                result.PageIndex = pageIndex;
            }
            #endregion

            #region 处理DQ入参变量，如果传入为空入参，抛出异常。否则获取该类型参数的默认值
            List<DataQueryParam> inputParamList = metadata.DataQueryParams.Where(x => x.IsInputParam == true && !x.IsDefault).ToList();
            if (inputParamList != null)
            {
                foreach (DataQueryParam item in inputParamList)
                {
                    ProcessQueryParam(metadata, item, paramList);
                }
            }
            #endregion

            #region 特殊处理DQ系统默认参数
            //处理高级查询过滤条件参数
            if (paramList.ContainsKey(AdvancedFilterString))
            {
                if (string.IsNullOrEmpty(Convert.ToString(paramList[AdvancedFilterString])))
                {
                    //如果未传入默认值，则拼接1=1字符串
                    paramList[AdvancedFilterString] = " 1=1 ";
                }
                else    //传入QueryPlan结构的Json
                {
                    try
                    {
                        advanceQuerySQL = new QueryPlanFilterHelper(Convert.ToString(paramList[AdvancedFilterString]), metadata).GetAdvanceQuerySQL();
                    }
                    catch (Exception e)
                    {

                        throw new FasException(DataAccessErrorCode.DataQuery.QueryPlanError, string.Format(CommonStringClassEngine.DQQueryService_ProcessParameter, metadata?.Code), e);
                    }
                }
            }
            else
            {
                //如果未传入默认值，则拼接1=1字符串
                paramList[AdvancedFilterString] = " 1=1 ";
            }

            //处理高级查询排序条件参数
            if (paramList.ContainsKey(AdvancedSortString))
            {
                if (!string.IsNullOrEmpty(Convert.ToString(paramList[AdvancedSortString])))
                {
                    advancedSorts = new QueryPlanSortHelper(Convert.ToString(paramList[AdvancedSortString]), metadata).GetAdvancedSorts();
                }
            }

            //处理掩码查询控制参数
            if (paramList.ContainsKey(ExtSettingString))
            {
                string maskQueryStr = Convert.ToString(paramList[ExtSettingString]);
                if (!string.IsNullOrEmpty(maskQueryStr))
                {
                    try
                    {
                        result.QueryExtSetting = JsonConvert.DeserializeObject<QueryExtSetting>(maskQueryStr);
                    }
                    catch
                    {
                        throw new Exception($"DQ:{metadata.Code} failed to deal with MaskSetting param, please check json content: {maskQueryStr}");
                    }
                }
            }

            result.AdvanceQuerySQL = advanceQuerySQL;
            result.AdvancedSorts = advancedSorts;
            #endregion

            #region DQ自定义变量对接表达式
            Dictionary<string, object> varList = new Dictionary<string, object>();
            var variableList = metadata.DataQueryParams.Where(x => x.IsInputParam == false).ToList();
            if (variableList != null && variableList.Count > 0)
            {
                var exprService = ExprService.GetInstance;
                foreach (var item in variableList)
                {
                    ExprResult expResult = exprService.DQExecute(item.DefaultValue, paramList);
                    if (expResult.State == ResultState.Success)
                    {
                        varList.Add(item.Code, expResult.Result);
                    }
                    else
                    {
                        throw new FasException(DataAccessErrorCode.ExpressRTP.Error, string.Format(CommonStringClassEngine.DQQueryService_ProcessParameter1, metadata?.Code, item.Code) + expResult.ErrMsg);
                    }
                }
            }

            Dictionary<string, FilterParam> pList = new Dictionary<string, FilterParam>();
            //将DQ查询传参转为FilterParam结构，数据库参数类型取DQParam的参数类型
            foreach (var item in paramList)
            {
                //跳过子查询的参数
                if (item.Value is Dictionary<string, object>) continue;
                pList[item.Key] = new FilterParam
                {
                    ParamCode = item.Key,
                    ParamValue = item.Value,
                };
                var inputParam = inputParamList.FirstOrDefault(par => par.Code == item.Key);
                if (inputParam != null)
                {
                    try
                    {
                        DataType dataType = DataTypeUtil.ConvertToDataType(inputParam.ParamType);
                        var databaseColumnType = DataTypeUtil.ConvertDatabaseColumnTypeFromDataType(dataType);
                        pList[item.Key].DatabaseColumnType = databaseColumnType;
                        pList[item.Key].DataType = dataType;
                    }
                    catch
                    {
                    }
                }
                //需要正确设置处理分页参数类型，否则PG数据库会报错
                //fix bug:Can't write CLR type System.Int32 with handler type TextHandler
                else if (item.Key == "PageIndex" || item.Key == "PageSize")
                {
                    pList[item.Key].DatabaseColumnType = DatabaseColumnType.Int;
                    pList[item.Key].DataType = DataType.Int;
                }
            }

            #region 参数处理
            //处理高级查询参数
            if (result.AdvanceQuerySQL?.ParamList != null && result.AdvanceQuerySQL.ParamList.Count > 0)
                pList = pList.Concat(result.AdvanceQuerySQL?.ParamList).ToDictionary(pair => pair.Key, pair => pair.Value);

            //父节点参数如何传递
            #endregion

            Dictionary<string, string> pTypeList = new Dictionary<string, string>();
            //自定义入参的类型
            var inputList = metadata.DataQueryParams.Where(x => x.IsInputParam == true).ToList();
            foreach (var item in inputList)
            {
                pTypeList.Add(item.Code, item.ParamType);
            }

            result.VariableList = varList;
            result.PrecessedParamList = pList;
            result.PrecessedParamTypeList = pTypeList;
            #endregion

            return result;
        }

        /// <summary>
        /// 校验DQ查询参数，并进行处理（boolean类型在db以Bit(1)形式存储，需要转为0，1）
        /// </summary>
        /// <param name="metadata"></param>
        /// <param name="par"></param>
        /// <param name="paramList"></param>
        /// <exception cref="FasException"></exception>
        private static void ProcessQueryParam(DataQuery metadata, DataQueryParam par, Dictionary<string, object> paramList)
        {
            //入参不包含该参数，或者该参数的值为null或者空字符串
            if (paramList.ContainsKey(par.Code) == false || string.IsNullOrEmpty(paramList[par.Code]?.ToString()))
            {
                //该入参为不可空入参，抛出异常
                if (!par.IsAllowNull)
                {
                    throw new FasException(DataAccessErrorCode.DataQuery.NullValue, string.Format(CommonStringClassEngine.DQQueryService_ProcessQueryParam2, metadata?.Code, (par.IsInputParam ? CommonStringClassEngine.DataAccessEngine_Argument : CommonStringClassEngine.DataAccessEngine_Variable), par.Code));
                }
                else
                {
                    //生成对应类型的参数的默认值
                    paramList[par.Code] = DataQueryHelper.GetTypeDefaultValue(par.ParamType);
                }
            }
            else
            {
                ItemCheckResult checkResult = DQValidateChecker.Check(par, paramList[par.Code]);
                if (!checkResult.Flag)
                {
                    throw new FasException(DataAccessErrorCode.DataQuery.NullValue, string.Format(CommonStringClassEngine.DQQueryService_ProcessQueryParam5, metadata?.Code, (par.IsInputParam ? CommonStringClassEngine.DataAccessEngine_Argument : CommonStringClassEngine.DataAccessEngine_Variable), par.Code, checkResult.Msg));
                }
                else
                {
                    //特殊处理string类型的入参，trim掉前后的空格
                    if (string.Equals(par.ParamType, DataType.String.ToString(), StringComparison.OrdinalIgnoreCase))
                    {
                        paramList[par.Code] = checkResult.ValidateCheckItem.ConvertValue?.ToString().Trim();
                    }
                    else
                    {
                        paramList[par.Code] = checkResult.ValidateCheckItem.ConvertValue;
                    }
                }
            }
        }
        #endregion

        #region 子查询相关

        /// <summary>
        /// 生成只包含子查询的父级查询关联列的sql，用于子查询IN语句中
        /// </summary>
        /// <param name="dataQuery"></param>
        /// <param name="subQuery"></param>
        /// <param name="SelectSqlStatement"></param>
        /// <returns></returns>
        private SelectSqlStatement GetInSqlStatement(DataQuery dataQuery, DataQuerySubQuery subQuery, SelectSqlStatement SelectSqlStatement)
        {
            var sql = SelectSqlStatement.Clone() as SelectSqlStatement;
            sql.SelectList = new SelectFieldListStatement();    //清空查询列
            var mainQueryColumn = dataQuery.GetMainQueryColumn(subQuery);
            var field = QuerySQLBuildStrategy.BuildSelectListField(mainQueryColumn, dataQuery);
            if (field == null)
                throw new FasException(DataAccessErrorCode.DataQuery.Error, $"it cant be hide or null in MainQuery({dataQuery.Code})'s identifier column:{mainQueryColumn.Name}");
            sql.SelectList.ChildCollection.Add(field);  //只将父查询的关联列放到查询结果中
            sql.OrderByCondition = null;
            sql.StaticOrderByCondition = null;
            sql.PageCount = 0;  //父级查询不加分页条件
            sql.PageIndex = 0;
            return sql;
        }

        /// <summary>
        /// 生成只包含子查询的父级查询关联列的sql，用于子查询Extist语句中
        /// </summary>
        /// <param name="current"></param>
        /// <param name="parent"></param>
        /// <param name="subQuery"></param>
        /// <param name="SelectSqlStatement"></param>
        /// <returns></returns>
        /// <exception cref="FasException"></exception>
        private SelectSqlStatement GetOutSqlStatement(DataQuery current, DataQuery parent, DataQuerySubQuery subQuery, SelectSqlStatement SelectSqlStatement)
        {
            var sql = SelectSqlStatement.Clone() as SelectSqlStatement;
            sql.SelectList = new SelectFieldListStatement();    //清空查询列
            var mainQueryColumn = current.GetSubQueryColumn(subQuery);
            var field = QuerySQLBuildStrategy.BuildSelectListField(mainQueryColumn, current);
            if (field == null)
                throw new FasException(DataAccessErrorCode.DataQuery.Error, $"it cant be hide or null in SubQuery({current.Code})'s foreign column:{mainQueryColumn.Name}");
            sql.SelectList.ChildCollection.Add(field);  //只将父查询的关联列放到查询结果中
            sql.OrderByCondition = null;
            sql.StaticOrderByCondition = null;
            sql.PageCount = 0;  //子级查询不加分页条件
            sql.PageIndex = 0;
            return sql;
        }

        #endregion

        /// <summary>
        /// 处理数值类参数，PG强制要求对象类型与SqlParameter对象中的NpgsqlDbType一致，所以必须做类型转换
        /// 修复bug： Can't write CLR type System.String with handler type Int32Handler
        /// </summary>
        /// <param name="databaseType"></param>
        /// <param name="dataType"></param>
        /// <param name="paramValue"></param>
        /// <returns></returns>
        private object GetNumericParameterValue(DatabaseType databaseType, DataType dataType, object paramValue)
        {
            var typer = DataTypeToolFactory.ValidAndConvertToObject(dataType);
            if (typer != null)
            {
                try
                {
                    return typer.ValidAndConvertToObject(paramValue, true);
                }
                catch (Exception e)
                {
                }
            }
            return Convert.ToString(paramValue);
        }
        #endregion
    }
}